
local skynet = require "skynet"
local mysql = require "skynet.db.mysql"
local socketchannel = require "skynet.socketchannel"
local utils = require "utils"

local mysql_host = skynet.getenv "mysql_host" or ""
local mysql_port = tonumber(skynet.getenv "mysql_port" or 3306)
local mysql_username = skynet.getenv "mysql_username" or ""
local mysql_password = skynet.getenv "mysql_password" or ""
local mysql_database = skynet.getenv "mysql_database" or ""

local db
local master

local tempsql = {sql = "",res = ""}

local function dbquery(sql, multirows)
	skynet.error("dbquery order : ",sql)
	if tempsql.sql == sql then
		return tempsql.res
	end

	local res = db:query(sql)
	if res['err'] then
		--error happen
		-- skynet.error("dbquery order : ",sql)
		skynet.error("mysqls", "mysql error", utils.dump(res))
		return nil
	end
	
	tempsql.sql = sql
	tempsql.res = res

	return res
end

local function getValueType(value) 
	local t = type(value);
		if t == "number" then
			if value % 1 == 0 then
				return "INT";
			else
				return "FLOAT";
			end
		else 
			return "VARCHAR";
		end
end

local function getValueString(value) 
	local t = type(value);
		if t == "number" then
			return value;
		else 
			return "\'" .. value .. "\'";
		end
end

local CMD = {}

function CMD.create(name, value)
	local sql_order = "CREATE TABLE " .. name;

	if type(value) == "table" then 
		local PRIMARY
		sql_order =  sql_order .. " ("
		for k, v in pairs(value) do  
			-- if PRIMARY then
			-- 	sql_createtable = sql_createtable..','
			-- end
			sql_order = sql_order .. k .. ' ' .. getValueType(v) .. " "..getValueString(v) .. ","
			if not PRIMARY then
				PRIMARY = k
			end
		end

		sql_order = sql_order .. "PRIMARY KEY ("..k..") )ENGINE=InnoDB DEFAULT CHARSET=utf8;"
	else
		sql_order =  sql_order .. " "..value ..";";
	end

	res = dbquery(sql_order)

	return res
end

--alter table id_name add age int,add address varchar(11);
function CMD.alter(name, value,taskid)
	local sql_order = "alter table " .. name

	local isvalue = false
	for k, v in pairs(value) do  
		local _order = "describe " .. name .. " " .. k
		res = dbquery(_order)
		-- skynet.error("res res : ",#res)
		if res and #res == 0 then
			if isvalue then
				sql_order = sql_order..','
			end
			sql_order = sql_order .. " add column ".. k .. ' ' .. v
			isvalue = true
		end
	end
	sql_order = sql_order..";";
	if isvalue then
		res = dbquery(sql_order)
	end
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end
	return true
end


function CMD.insert(name, value,taskid)
	skynet.error("insert ",name,taskid)
	-- utils.dumpprint(value)
	local sql_order = "INSERT INTO " .. name .. " (";

	local isvalue = false
	for k, v in pairs(value) do  
		if isvalue then
			sql_order = sql_order..','
		end
        sql_order = sql_order .. k
		isvalue = true;
    end

	sql_order = sql_order .. " ) VALUES ("

	isvalue = false
	for k, v in pairs(value) do  
		if isvalue then
			sql_order = sql_order..','
		end
        sql_order = sql_order .. getValueString(v)
		isvalue = true;
    end

	sql_order = sql_order.." );";
	res = dbquery(sql_order)
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end

	return true
end

-- delete("player","id=1");
function CMD.delete(name, order,taskid)
	-- skynet.error("insert ",name,value,taskid)
	local sql_order = "delete from " .. name

	if order then
		sql_order = sql_order .. " where "..order
	end

	res = dbquery(sql_order)
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end

	return true
end

-- 替换数据 replace("player",{id:1,name:"wanjia",level:1}) //完全替换一个数据,会根据主键替换
function CMD.replace(name, value,taskid)
	local sql_order = "replace into " .. name;

	sql_order = sql_order.." (";
	local isvalue = false
	for k, v in pairs(value) do  
		if isvalue then
			sql_order = sql_order..','
		end
        sql_order = sql_order .. k
		isvalue = true;
    end

	sql_order = sql_order .. " ) VALUES ("

	isvalue = false
	for k, v in pairs(value) do  
		if isvalue then
			sql_order = sql_order..','
		end
        sql_order = sql_order .. getValueString(v)
		isvalue = true;
    end

	sql_order = sql_order.." );";

	res = dbquery(sql_order)
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end

	return true
end

-- 更新数据 update("player",{name:"wanjia",level:1},"name='玩家'");//多一个参数,指令,可以填写需要更新的数据范围
function CMD.update(name, value,taskid,order)
	local sql_order = "update " .. name;

	sql_order = sql_order.." set ";
	local isvalue = false
	for k, v in pairs(value) do  
		if isvalue then
			sql_order = sql_order..','
		end
        sql_order = sql_order .. k .. "=".. getValueString(v)
		isvalue = true;
    end


	if order then
		sql_order = sql_order .. " where "..order
	end

	res = dbquery(sql_order)
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end

	return true
end

-- 要获取的keys,key 全部 * name
function CMD.getData(name, keys,order)
	local sql_order = "select " .. keys .. " from " .. name

	if order then
		sql_order = sql_order .. " where "..order
	end

	res = dbquery(sql_order)
	-- skynet.error("getData res : ",utils.dump(res))
	return res
end

function CMD.setData(name, value,taskid,order)
	local sql_order = "select " .. "uid" .. " from " .. name
	sql_order = sql_order .. " where "..order
	res = dbquery(sql_order)
	if res then
		if #res == 0 then
			CMD.insert(name, value,taskid)
		else
			CMD.update(name, value,taskid,order)
		end
	end
	if taskid then
		skynet.send(master, "lua", "finish_task", taskid)
	end
	return true
end


function CMD.executiveOrder(sql_order)
	res = dbquery(sql_order)
	return res
end


function CMD.init(persistent_master)
	master = persistent_master
	return true
end

skynet.start(function()
	skynet.dispatch("lua", function(_, _, cmd, ...) 
		local f = assert(CMD[cmd])
		local result = f(...)

		if result ~= nil then
			skynet.ret(skynet.pack(result))
		end
	end)

	local function on_connected(db)
		db:query("set names utf8")
	end

	-- skynet.error("mysqlagent ",mysql_host,mysql_port,mysql_database,mysql_username,mysql_password)
	local ok
	ok, db = pcall(mysql.connect, {
		host = mysql_host,
		port = mysql_port,
		database = mysql_database,
		user = mysql_username,
		password = mysql_password,
		max_packet_size = 1024 * 1024,
		on_connect = on_connected
	})

	if not ok or not db then
		skynet.error("persistent_slave", "connect to mysql failed")
	end

	--keep mysql alive
	skynet.fork(function()
		while true do
			if db and db ~= socketchannel.error then
				local sql = string.format("select %s", skynet.time())
				db:query(sql)
			end

			--sleep 60 seconds
			skynet.sleep(6000)
		end
	end)
end)


